﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.CSharp;
using System.CodeDom.Compiler;
using System.Reflection;
using Microsoft.SqlServer.Management.Smo;

namespace www.serviciipeweb.ro.CDC
{
    public interface IModifiedTable
    {
        System.DateTime ModifiedDate{get;set;}
        string SQLOperation { get; set; }
    }
    public enum Operation
    {
        Delete=1,
        Insert =2,
        ModifiedValue=3,
        Update=4
    }
    public class ResultTable
    {
        public object Value { get; set; }
        public Type columnType { get; set; }
    }
    public class TableChanges
    {
        public TableCDC parent;
        public TableChanges(TableCDC cdc)
        {
            parent = cdc;
            ModifiedValues = new Dictionary<string, ResultTable>();
        }

        public byte[] start_lsn { get; internal set; }
        public byte[] seqval { get; internal set; }
        public Operation SQLOperation;
        public byte[] update_mask;
        public Dictionary<string, ResultTable> ModifiedValues;
        public DateTime Start
        {
            get
            {
                
                return SQLHelperLSN.LSN2Time(start_lsn,parent.parent.Parent.Name).Value;
            }
        }

        internal void FromReader(SqlDataReader sr)
        {
            for (int i = 0; i < sr.FieldCount; i++)
            {
                switch (sr.GetName(i))
                {
                    case "__$end_lsn":
                        //according to documentation end lsn is always null
                        break;
                    case "__$start_lsn":
                        this.start_lsn = SqlUtilities<byte[]>.GetValueOrDefault(sr["__$start_lsn"]);
                        break;
                    case "__$seqval":
                        this.seqval = SqlUtilities<byte[]>.GetValueOrDefault(sr["__$seqval"]);
                        break;
                    case "__$operation":
                        this.SQLOperation = (Operation)SqlUtilities<int>.GetValueOrDefault(sr["__$operation"]);
                        break;
                    case "__$update_mask":
                        this.update_mask = SqlUtilities<byte[]>.GetValueOrDefault(sr["__$update_mask"]);
                        ////console.writeline(sr["__$update_mask"].GetType());
                        break;
                    default:
                        ModifiedValues.Add(sr.GetName(i),
                            new ResultTable(){columnType = sr.GetFieldType(i),Value= sr[i]});

                        break;
                }
            }

            
            

        }
        public static string NamePropertyOrClass(string name)
        {
            //TODO : reserved keywords not taken into consideration
            char[] val = name.ToCharArray();
            name = "";
            foreach (char c in val)
            {
                if (char.IsLetterOrDigit(c))
                    name += c;
                else
                    name += "_";
            }
            if (!char.IsLetter(name, 0))
                name = "A" + name;

            return name;

        }
        public static Assembly FromTableChanges(TableChanges tc, out string NameClass)
        {
            CSharpCodeProvider ccp = new CSharpCodeProvider();
            string[] reference = { "System.dll", "CDCObjects.dll" };
            CompilerParameters cp = new CompilerParameters(reference);
            NameClass = "InheritTC_" + NamePropertyOrClass( tc.parent.Name ) ;
            string s = @" public class " + NameClass + @" : www.serviciipeweb.ro.CDC.IModifiedTable
                {
                    private www.serviciipeweb.ro.CDC.TableChanges  m_TC;
                    public " + NameClass + @"(www.serviciipeweb.ro.CDC.TableChanges tc)                        
                    {
                        m_TC=tc;
                    }
            
                    public System.DateTime ModifiedDate
                    {
                        get{return m_TC.Start;}
                        set{string g=value.ToString();}//maybe later?
                    }
                    public string SQLOperation
                    {
                        get{return m_TC.SQLOperation.ToString();}
                        set{string g=value.ToString();}//maybe later?
                    }
                    ";

            foreach (string key in tc.ModifiedValues.Keys)
            {
                
                ResultTable rs = tc.ModifiedValues[key];
                s += Environment.NewLine;
                string TypeName = rs.columnType.FullName;
                if (rs.columnType.IsValueType)
                {
                    //add for null
                    TypeName = TypeName + "?";
                }
                s += "public " + TypeName + " " + NamePropertyOrClass(key);
                s += Environment.NewLine;
                s += "{";
                s += Environment.NewLine;
                s += "get {";

                s += " www.serviciipeweb.ro.CDC.ResultTable o = m_TC.ModifiedValues[" + "\"" + key + "\"" + "] as www.serviciipeweb.ro.CDC.ResultTable; ";
                s += @" if (o.Value != null && o.Value != System.DBNull.Value) 
                        {
                        return (" + rs.columnType.FullName + @") o.Value ;
                        } else 
                        {
                            return null;
                        }";
                s += "} //end get";
                s += Environment.NewLine;
                s += "}";


            }

            s += "    }";
            cp.GenerateExecutable = false;
            cp.GenerateInMemory = true;
            cp.IncludeDebugInformation = true;

            CompilerResults cr = ccp.CompileAssemblyFromSource(cp, s);
            if (cr.Errors.Count > 0)//TODO : custom exception
                throw new Exception("error generate" + cr.Errors[0].ErrorText);

            return cr.CompiledAssembly;
        }
    }

   
    public class TableChangesCollection : List<TableChanges>
    {
        public TableChangesCollection(DateTime Begin, DateTime End, TableCDC cdc)
        {
            dateBegin = Begin;
            dateEnd = End;
            tCDC = cdc;
        }
        public TableChangesCollection(DateTime Begin, TableCDC cdc):this(Begin,DateTime.MaxValue,cdc)
        {

        }
        public TableChangesCollection(TableCDC cdc)
            : this(cdc.FirstCapture.Value, cdc)
        {
        }

        public DateTime dateBegin { get; set; }
        public DateTime dateEnd { get; set; }
        public TableCDC tCDC { get; internal set; }

        public void Initialize()
        {
            DateTime dtEnd = (dateEnd == DateTime.MaxValue) ? DateTime.Now.AddDays(1) : dateEnd;

            byte[] start = SQLHelperLSN.LSN_Start(dateBegin, tCDC.parent.Parent.Name);
            byte[] end = SQLHelperLSN.LSN_END(dtEnd, tCDC.parent.Parent.Name);
            using (SqlConnection scon = new SqlConnection())
            {
                
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    //sc.CommandText = @"SELECT * FROM cdc.[fn_cdc_get_all_changes_" + tCDC.Name + @"](@from_lsn, @to_lsn, 'all')";
                    sc.CommandText = "SELECT * FROM " + tCDC.parent.Parent.Name+ ".cdc." + tCDC.Name + @"_CT t
                where 
                (t.__$start_lsn <= @to_lsn)
		                and (t.__$start_lsn >= @from_lsn)";
                    sc.Parameters.AddWithValue("from_lsn", start);
                    sc.Parameters.AddWithValue("to_lsn", end);
                    sc.Connection = scon;

                    using (SqlDataReader sr = sc.ExecuteReader())
                    {

                        while (sr.Read())
                        {
                            TableChanges tc = new TableChanges(this.tCDC);
                            tc.FromReader(sr);
                            this.Add(tc);
                        }
                    }

                }
            }
        

        }


        public List<object> InheritedData()
        {
            if (this.Count == 0)
                return null;

            string NameClass;
            Assembly a = TableChanges.FromTableChanges(this[0],out NameClass);

            List<object> ts = new List<object>();
            this.ForEach
                (
                (t) => 
                {
                    Type gen = a.GetExportedTypes()[0];
                    object o = Activator.CreateInstance(gen, t);
                
                    ts.Add(o); 
                }
                );
            return ts;
        }

        public void RestoreTableToDate(DateTime DateToRestore, string NewNameTable)
        {
            if(!tCDC.parent.HasPrimaryKeyIdentity())
                throw new ArgumentException(" no primary key identity for table " + tCDC.parent.Name);
            string UseDB = "use " + tCDC.parent.Parent.Name + ";";

            if (this.Count == 0)
                return;

            List<object> data = InheritedData();

            Type tDef = data[0].GetType();
            string PKIDentity = tCDC.parent.PrimaryKey().IndexedColumns[0].Name;
            string propnameID = TableChanges.NamePropertyOrClass(PKIDentity);


            string sqlInsert = "set identity_insert "+  NewNameTable +" on;";
            sqlInsert += " insert into " + NewNameTable + "(";
            string sqlValues = " values(";
            string sqlUpdate = "update " + NewNameTable + " set ";
            string sqlDelete = "delete from " + NewNameTable + " where " + PKIDentity + "= @" + propnameID;
            Dictionary<string, MethodInfo> dict = new Dictionary<string, MethodInfo>();

            foreach (Column c in tCDC.parent.Columns)
            {
                sqlInsert += c.Name + ",";
                sqlValues += "@" + TableChanges.NamePropertyOrClass(c.Name) + ",";

                if (c.Name != PKIDentity)
                {
                    sqlUpdate += c.Name + "=@" + TableChanges.NamePropertyOrClass(c.Name) + ",";
                }

                dict.Add(TableChanges.NamePropertyOrClass(c.Name), tDef.GetProperty(TableChanges.NamePropertyOrClass(c.Name)).GetGetMethod());

            }
            
            sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 1);//remove ,

            sqlValues = sqlValues.Substring(0, sqlValues.Length - 1);
            sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1);
            
            sqlInsert += ")" + sqlValues + ")";

            sqlUpdate += " where " + PKIDentity + " = @" + propnameID;
            using (SqlConnection sc = new SqlConnection())
            {
                sc.ConnectionString = SQLHelperLSN.SqlConnection;
                sc.Open();
                using (SqlTransaction st = sc.BeginTransaction())
                {
                    using (SqlCommand so = new SqlCommand())
                    {
                        so.CommandType = CommandType.Text;
                        so.CommandText = UseDB +"select * into " + NewNameTable + " from  " + tCDC.parent.Name;
                        so.Connection = sc;
                        so.Transaction = st;
                        so.ExecuteNonQuery();
                    
                    }
                    
                    for (int i = data.Count - 1; (i >= 0); i--)
                    {
                        if (this[i].Start < DateToRestore)
                            break;

                        using (SqlCommand so = new SqlCommand())
                        {
                            so.CommandType = CommandType.Text;
                            string sql="";
                            switch (this[i].SQLOperation)
                            {
                                case Operation.Delete:
                                    so.CommandText = UseDB + sqlInsert;
                                    foreach(string propname in dict.Keys)
                                    {
                                        so.Parameters.AddWithValue(propname,dict[propname].Invoke(data[i],null));
                                    }
                                    break;
                                case Operation.Insert:
                                    so.CommandText = UseDB + sqlDelete;
                                    so.Parameters.AddWithValue(propnameID,SQLHelperLSN.Null2DBNull( dict[propnameID].Invoke(data[i], null)));
                                    break;
                                case Operation.Update:
                                    //Do nothing,it is the modify that counts... primary values///
                                    continue;
                                case Operation.ModifiedValue:
                                    so.CommandText = UseDB + sqlUpdate;
                                    foreach (string propname in dict.Keys)
                                    {


                                        so.Parameters.AddWithValue(propname, SQLHelperLSN.Null2DBNull(dict[propname].Invoke(data[i], null)));

                                    }
                                    break;
                            }
                            
                            so.Connection=sc;
                            so.Transaction=st;
                            int iRows= so.ExecuteNonQuery();
                            if (iRows != 1)
                                throw new ArgumentException(" not 1 row, but " + iRows + " modified"); 
                        }
                    }
                    st.Commit();
                }
            }
        }

    
       
         
    }
    
}
